﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OracleClient;

namespace Tony.Oracle
{
	/// <summary>
	/// 用ADO.Net对Oracle数据库操作函数的封装
	/// <para>数据库链接字符串在WebConfig中设置，默认名字“ConnString”，通过设置属性“ConnString”可以修改链接名</para>
	/// </summary>
	public class DBOperation : BaseClass
	{
		#region 属性

		private string strConn = "";		// 链接字符串，在WebConfig中设置

		public string ConnString
		{
			get { return ConnString; }
			set { ConnString = value.Trim(); }
		}
			
		#endregion
		
		#region 构造函数

		public DBOperation()
			: base()
		{
			strConn = "ConnString";
		}

		#endregion


		/// <summary>
		/// 获得数据库链接对象
		/// </summary>
		private OracleConnection GetConnection()
		{
			OracleConnection lconn;
			//lconn.ConnectionString = "server=localhost;database=Template;uid=sa;pwd='sa'";
			
			try
			{
				//lconn = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["ConnString"]);
				lconn = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings[strConn]);
			}
			catch (Exception ex)
			{
				//listErrors.Add(new Error(ex.Message));
				AddError(ex.Message);
				lconn = null;
			}
			finally
			{
				
			}

			return lconn;
		}

		/// <summary>
		/// 执行一个SQL语句，并返回受影响的行数
		/// </summary>
		public int ExecuteSQL(string strSQL)
		{
			OracleConnection conn = new OracleConnection();
			int n = 0;

			try
			{
				conn = GetConnection();
				conn.Open();

				OracleCommand cmd = new OracleCommand(strSQL, conn);
				
				n = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				AddError(ex.Message);
			}
			finally
			{
				if (conn != null)
				{
					conn.Dispose();
				}

			}
			return n;
		}

		/// <summary>
		/// 执行一个SQL语句，并返回受影响的行数，可操作带CLOB字段的SQL语句
		/// <param name="strPName">变量名</param>
		/// <param name="strPValue">变量值</param>
		/// </summary>
		public int ExecuteSQL(string strSQL, string strPName, string strPValue)
		{
			OracleConnection conn = new OracleConnection();
			int n = 0;

			try
			{
				conn = GetConnection();
				conn.Open();

				OracleCommand cmd = new OracleCommand(strSQL, conn);

				if (strPValue != "")
				{
					OracleParameter para;

					para = new OracleParameter(strPName, OracleType.Clob);
					para.Value = strPValue;
					cmd.Parameters.Add(para);
				}

				n = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				AddError(ex.Message);
			}
			finally
			{
				if (conn != null)
				{
					conn.Dispose();
				}

			}
			return n;
		}

		/// <summary>
		/// 根据查询条件查询，返回DataTable
		/// <returns>查询到结果返回DataTable，没有查询到返回null</returns>
		/// </summary>
		public DataTable QueryDataTable(string strSQL)
		{
			OracleConnection conn = new OracleConnection();
			DataSet ds = new DataSet();

			try
			{
				conn = GetConnection();
				conn.Open();
				OracleCommand cmd = new OracleCommand(strSQL, conn);
				OracleDataAdapter da = new OracleDataAdapter(cmd);
				da.Fill(ds);
				
			}
			catch (OracleException ex)
			{
				//ShowMessageAlert.ShowError(ex.Message);
				AddError(ex.Message);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
				}
			}

			if (ds.Tables.Count > 0)
			{
				return ds.Tables[0];
			}
			else
			{
				return null;
			}
		}

	}
}